package spirit1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

//insertSungJuk(){};
public class EX01_sungjukDAO implements EX01_ISungjukDAO{
	
	private String DRV ="oracle.jdbc.driver.OracleDriver";
	private String URL ="jdbc:oracle:thin:spirit/spirit@112.216.253.125:1521:xe";
	private String insertSQL = "insert into EX01Sungjuk values(exam.nextval,?,?,?,?,?,?,?)";
	private String selectSQL = "select * from EX01SUNGJUK order by sjno desc";
	private String updateSQL = "update EX01SUNGJUK set kor = ?, set eng = ? set mat = ? where mno = ?";
	private String deleteSQL = "delete EX01SUNGJUK where sjno = ?";
	@Override
	public boolean insertSungjuk(EX01_Sungjukvo ac) {
	Connection conn = null;
	PreparedStatement pstmt = null;
	boolean result = false;
	
	try{
		conn = this.openDatebase();
		pstmt = conn.prepareStatement(insertSQL);
		pstmt.setString(1,ac.getName());
		pstmt.setInt(2,ac.getKor());
		pstmt.setInt(3,ac.getEng());
		pstmt.setInt(4,ac.getMat());
		pstmt.setInt(5,ac.getTot());
		pstmt.setFloat(6,ac.getAvg());
		pstmt.setString(7,ac.getGrade());
	
		pstmt.executeUpdate();
		result = true;
	}catch(Exception ex){
		ex.printStackTrace();
	}finally{
		closeDatebase(conn,pstmt,null);
	}
	return result;			
	}
	private Connection openDatebase(){
		try{
			Class.forName(DRV);
			return DriverManager.getConnection(URL);
		}catch(Exception ex){
			ex.printStackTrace();
		}
		return null;
	}
	private void closeDatebase(
	Connection c, PreparedStatement p, ResultSet r){
		if(r != null)try{r.close(); r = null;}catch(Exception e){}
		if(p != null)try{p.close(); p = null;}catch(Exception e){}
		if(c != null)try{c.close(); c = null;}catch(Exception e){}
	}
	@Override
	public List<EX01_Sungjukvo> selectSungjuk() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<EX01_Sungjukvo> result = new ArrayList<>();
		try{
			conn = openDatebase();
			pstmt = conn.prepareStatement(selectSQL);
			rs = pstmt.executeQuery();
			while(rs.next()){
				EX01_Sungjukvo ac = new EX01_Sungjukvo();
				ac.setName(rs.getString("Name"));
				ac.setKor(rs.getInt("Kor"));
				ac.setEng(rs.getInt("Eng"));
				ac.setMat(rs.getInt("Mat"));
				ac.setTot(rs.getInt("Tot"));
				ac.setAvg(rs.getFloat("Avg"));
				ac.setGrade(rs.getString("Grade"));
	
				result.add(ac);
			}
		}catch(Exception ex){
			ex.printStackTrace();
			
		}finally{
			closeDatebase(conn,pstmt,rs);
		}
		return result;
	}
	@Override
	public boolean updateSungjuk(EX01_Sungjukvo ac) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		try{
			conn = openDatebase();
			pstmt = conn.prepareStatement(updateSQL);
			pstmt.setInt(1, ac.getKor());
			pstmt.setInt(2, ac.getEng());
			pstmt.setInt(3, ac.getMat());
			int cnt = pstmt.executeUpdate();
			if(cnt == 0)
				System.out.println("명령은 실행되었지만 , 수정결과가없음");
			result = true;
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			closeDatebase(conn,pstmt,null);
		}
		return result;
	}
	@Override
	public boolean deleteSungjuk(EX01_Sungjukvo ac) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		try{
			conn = openDatebase();
			pstmt = conn.prepareStatement(deleteSQL);
			pstmt.setString(1, ac.getSjno());
			int cnt = pstmt.executeUpdate();
			if(cnt == 0)
				System.out.println("명령은 실행 되었지만 , 삭제된게 없습니다");
			result = true;
		}catch(Exception ex){
			ex.printStackTrace();
			
		}finally{
			closeDatebase(conn, pstmt, null);
		}
		return result;
	}
}